﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class CarrierDA : ICarrierDA
    {
        private string ADDRESS = "@ADDRESS";
        private string B_ADDRESS = "@B_ADDRESS";
        private string B_CITY = "@B_CITY";
        private string B_CONTACT1 = "@B_CONTACT1";
        private string B_CONTACT2 = "@B_CONTACT2";
        private string B_COUNTRY = "@B_COUNTRY";
        private string B_COUNTRY_CODE = "@B_COUNTRY_CODE";
        private string B_EMAIL1 = "@B_EMAIL1";
        private string B_EMAIL2 = "@B_EMAIL2";
        private string B_FAX1 = "@B_FAX1";
        private string B_FAX2 = "@B_FAX2";
        private string B_PHONE1 = "@B_PHONE1";
        private string B_PHONE2 = "@B_PHONE2";
        private string B_STATE = "@B_STATE";
        private string B_ZIP = "@B_ZIP";
        private string CARRIER_ID = "@CARRIER_ID";
        private string CITY = "@CITY";
        private string COMPANY = "@COMPANY";
        private string CONTACT1 = "@CONTACT1";
        private string CONTACT2 = "@CONTACT2";
        private string COUNTRY = "@COUNTRY";
        private string COUNTRY_CODE = "@COUNTRY_CODE";
        private string CREATED_BY = "@CREATED_BY";
        private string DESCRIPTION = "@DESCRIPTION";
        private string EMAIL1 = "@EMAIL1";
        private string EMAIL2 = "@EMAIL2";
        private string FAX1 = "@FAX1";
        private string FAX2 = "@FAX2";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string PHONE1 = "@PHONE1";
        private string PHONE2 = "@PHONE2";
        private string REMARK = "@REMARK";
        private string SQL_CHECK_AUTHORIZATION_CARRIER_USERID = "SELECT COUNT(1) FROM @_@CARRIER C INNER JOIN SYSADM.USER_CARRIER UC ON C.CARRIER_ID = UC.CARRIER_ID AND UC.USER_ID=@USER_ID  WHERE C.CARRIER_ID = @CARRIER_ID";
        private string SQL_CHECK_CARRIER_ID_UNIQUE = " SELECT COUNT(1) FROM @_@CARRIER   WHERE CARRIER_ID = @CARRIER_ID ";
        private string SQL_DELETE_CARRIER = " DELETE FROM @_@CARRIER WHERE CARRIER_ID = @CARRIER_ID ";
        private string SQL_INSERT_CARRIER = " INSERT INTO @_@CARRIER ( WH_ID, CARRIER_ID, COMPANY, DESCRIPTION, VAT, CITY, STATE, ZIP, COUNTRY, COUNTRY_CODE, ADDRESS, CONTACT1, CONTACT2, PHONE1, PHONE2, FAX1, FAX2, EMAIL1, EMAIL2, B_CITY, B_STATE, B_ZIP, B_COUNTRY, B_COUNTRY_CODE, B_ADDRESS, B_CONTACT1, B_CONTACT2, B_PHONE1, B_PHONE2, B_FAX1, B_FAX2, B_EMAIL1, B_EMAIL2, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @CARRIER_ID, @COMPANY, @DESCRIPTION, @VAT, @CITY, @STATE, @ZIP, @COUNTRY, @COUNTRY_CODE, @ADDRESS, @CONTACT1, @CONTACT2, @PHONE1, @PHONE2, @FAX1, @FAX2, @EMAIL1, @EMAIL2, @B_CITY, @B_STATE, @B_ZIP, @B_COUNTRY, @B_COUNTRY_CODE, @B_ADDRESS, @B_CONTACT1, @B_CONTACT2, @B_PHONE1, @B_PHONE2, @B_FAX1, @B_FAX2, @B_EMAIL1, @B_EMAIL2, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_CARRIER = " SELECT  C.ROWID, C.WH_ID, C.CARRIER_ID, C.COMPANY, C.DESCRIPTION, C.VAT, C.CITY, C.STATE, C.ZIP, C.COUNTRY, C.COUNTRY_CODE, C.ADDRESS, C.CONTACT1, C.CONTACT2, C.PHONE1, C.PHONE2, C.FAX1, C.FAX2, C.EMAIL1, C.EMAIL2, C.B_CITY, C.B_STATE, C.B_ZIP, C.B_COUNTRY, C.B_COUNTRY_CODE, C.B_ADDRESS, C.B_CONTACT1, C.B_CONTACT2, C.B_PHONE1, C.B_PHONE2, C.B_FAX1, C.B_FAX2, C.B_EMAIL1, C.B_EMAIL2, C.UDF1, C.UDF2, C.UDF3, C.UDF4, C.UDF5, C.UDF6, C.UDF7, C.UDF8, C.UDF9, C.UDF10,C.REMARK, C.IS_ACTIVE, C.TS, C.CREATED_BY, C.CREATED_DATE, C.UPDATED_BY, C.UPDATED_DATE  FROM  @_@CARRIER C INNER JOIN SYSADM.USER_CARRIER UC ON C.CARRIER_ID = UC.CARRIER_ID AND UC.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_CARRIER = " UPDATE @_@CARRIER SET WH_ID = @WH_ID, COMPANY = @COMPANY, DESCRIPTION = @DESCRIPTION, VAT = @VAT, CITY = @CITY, STATE = @STATE, ZIP = @ZIP, COUNTRY = @COUNTRY, COUNTRY_CODE = @COUNTRY_CODE, ADDRESS = @ADDRESS, CONTACT1 = @CONTACT1, CONTACT2 = @CONTACT2, PHONE1 = @PHONE1, PHONE2 = @PHONE2, FAX1 = @FAX1, FAX2 = @FAX2, EMAIL1 = @EMAIL1, EMAIL2 = @EMAIL2, B_CITY = @B_CITY, B_STATE = @B_STATE, B_ZIP = @B_ZIP, B_COUNTRY = @B_COUNTRY, B_COUNTRY_CODE = @B_COUNTRY_CODE, B_ADDRESS = @B_ADDRESS, B_CONTACT1 = @B_CONTACT1, B_CONTACT2 = @B_CONTACT2, B_PHONE1 = @B_PHONE1, B_PHONE2 = @B_PHONE2, B_FAX1 = @B_FAX1, B_FAX2 = @B_FAX2, B_EMAIL1 = @B_EMAIL1, B_EMAIL2 = @B_EMAIL2, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE CARRIER_ID = @CARRIER_ID  ";
        private string STATE = "@STATE";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VAT = "@VAT";
        private string WH_ID = "@WH_ID";
        private string ZIP = "@ZIP";

        public bool CheckAuthorizationCarrierUserID(string carrierID, string whLoginID, PubParasInfo pubParasInfo)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CARRIER_ID", carrierID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_AUTHORIZATION_CARRIER_USERID.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckAuthorizationCarrierUserID(DataBase dataBase, DbTransaction tran, string carrierID, string whLoginID, PubParasInfo pubParasInfo)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CARRIER_ID", carrierID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_AUTHORIZATION_CARRIER_USERID.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckCarrierIDUnique(string carrierID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CARRIER_ID", carrierID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_CARRIER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckCarrierIDUnique(DataBase dataBase, DbTransaction tran, string carrierID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CARRIER_ID", carrierID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_CARRIER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteCarrier(List<string> carrierID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < carrierID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CARRIER_ID", carrierID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CARRIER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteCarrier(DataBase dataBase, DbTransaction tran, List<string> carrierID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < carrierID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CARRIER_ID", carrierID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CARRIER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllCarrier(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CARRIER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllCarrier(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CARRIER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public CarrierInfo GetCarrierByID(string carrierID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CARRIER.Replace("@_@", whLoginID) + " WHERE C.CARRIER_ID = @CARRIER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CARRIER_ID", carrierID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            CarrierInfo carrierInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    carrierInfo = new CarrierInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["CARRIER_ID"].ToString(), reader["COMPANY"].ToString(), reader["DESCRIPTION"].ToString(), reader["VAT"].ToString(), reader["CITY"].ToString(), reader["STATE"].ToString(), reader["ZIP"].ToString(), reader["COUNTRY"].ToString(), reader["COUNTRY_CODE"].ToString(), reader["ADDRESS"].ToString(), reader["CONTACT1"].ToString(), reader["CONTACT2"].ToString(), reader["PHONE1"].ToString(), reader["PHONE2"].ToString(), reader["FAX1"].ToString(), reader["FAX2"].ToString(), reader["EMAIL1"].ToString(), reader["EMAIL2"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return carrierInfo;
        }

        public CarrierInfo GetCarrierByID(DataBase dataBase, DbTransaction tran, string carrierID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CARRIER.Replace("@_@", whLoginID) + " WHERE C.CARRIER_ID = @CARRIER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CARRIER_ID", carrierID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            CarrierInfo carrierInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                carrierInfo = new CarrierInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["CARRIER_ID"].ToString(), reader["COMPANY"].ToString(), reader["DESCRIPTION"].ToString(), reader["VAT"].ToString(), reader["CITY"].ToString(), reader["STATE"].ToString(), reader["ZIP"].ToString(), reader["COUNTRY"].ToString(), reader["COUNTRY_CODE"].ToString(), reader["ADDRESS"].ToString(), reader["CONTACT1"].ToString(), reader["CONTACT2"].ToString(), reader["PHONE1"].ToString(), reader["PHONE2"].ToString(), reader["FAX1"].ToString(), reader["FAX2"].ToString(), reader["EMAIL1"].ToString(), reader["EMAIL2"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return carrierInfo;
        }

        public DataSet GetCarrierByQueryList(List<string> sqlWhere, CarrierQueryEntity carrierQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CARRIER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!carrierQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, carrierQuery.CurrentPage, carrierQuery.PageSize, carrierQuery.SortField, carrierQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetCarrierByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, CarrierQueryEntity carrierQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CARRIER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!carrierQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, carrierQuery.CurrentPage, carrierQuery.PageSize, carrierQuery.SortField, carrierQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertCarrier(CarrierInfo carrierInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    carrierInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Carrier_Parameters(carrierInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CARRIER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertCarrier(DataBase dataBase, DbTransaction tran, CarrierInfo carrierInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    carrierInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Carrier_Parameters(carrierInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CARRIER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Carrier_Parameters(CarrierInfo carrierInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.CARRIER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.COMPANY, SqlDbType.VarChar, 50), new SqlParameter(this.DESCRIPTION, SqlDbType.VarChar, 50), new SqlParameter(this.VAT, SqlDbType.VarChar, 20), new SqlParameter(this.CITY, SqlDbType.VarChar, 50), new SqlParameter(this.STATE, SqlDbType.VarChar, 20), new SqlParameter(this.ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.FAX1, SqlDbType.VarChar, 20), 
                new SqlParameter(this.FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.EMAIL1, SqlDbType.VarChar, 60), new SqlParameter(this.EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.B_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.B_STATE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.B_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.B_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.B_CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.B_CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.B_PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.B_PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX1, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.B_EMAIL1, SqlDbType.VarChar, 60), 
                new SqlParameter(this.B_EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x7d0), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(carrierInfo.WhID))
            {
                paramArray[0].Value = carrierInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = carrierInfo.CarrierID;
            if (!string.IsNullOrEmpty(carrierInfo.Company))
            {
                paramArray[2].Value = carrierInfo.Company;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Description))
            {
                paramArray[3].Value = carrierInfo.Description;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Vat))
            {
                paramArray[4].Value = carrierInfo.Vat;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.City))
            {
                paramArray[5].Value = carrierInfo.City;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.State))
            {
                paramArray[6].Value = carrierInfo.State;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Zip))
            {
                paramArray[7].Value = carrierInfo.Zip;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Country))
            {
                paramArray[8].Value = carrierInfo.Country;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.CountryCode))
            {
                paramArray[9].Value = carrierInfo.CountryCode;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Address))
            {
                paramArray[10].Value = carrierInfo.Address;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Contact1))
            {
                paramArray[11].Value = carrierInfo.Contact1;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Contact2))
            {
                paramArray[12].Value = carrierInfo.Contact2;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Phone1))
            {
                paramArray[13].Value = carrierInfo.Phone1;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Phone2))
            {
                paramArray[14].Value = carrierInfo.Phone2;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Fax1))
            {
                paramArray[15].Value = carrierInfo.Fax1;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Fax2))
            {
                paramArray[0x10].Value = carrierInfo.Fax2;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Email1))
            {
                paramArray[0x11].Value = carrierInfo.Email1;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Email2))
            {
                paramArray[0x12].Value = carrierInfo.Email2;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BCity))
            {
                paramArray[0x13].Value = carrierInfo.BCity;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BState))
            {
                paramArray[20].Value = carrierInfo.BState;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BZip))
            {
                paramArray[0x15].Value = carrierInfo.BZip;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BCountry))
            {
                paramArray[0x16].Value = carrierInfo.BCountry;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BCountryCode))
            {
                paramArray[0x17].Value = carrierInfo.BCountryCode;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BAddress))
            {
                paramArray[0x18].Value = carrierInfo.BAddress;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BContact1))
            {
                paramArray[0x19].Value = carrierInfo.BContact1;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BContact2))
            {
                paramArray[0x1a].Value = carrierInfo.BContact2;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BPhone1))
            {
                paramArray[0x1b].Value = carrierInfo.BPhone1;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BPhone2))
            {
                paramArray[0x1c].Value = carrierInfo.BPhone2;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BFax1))
            {
                paramArray[0x1d].Value = carrierInfo.BFax1;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BFax2))
            {
                paramArray[30].Value = carrierInfo.BFax2;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BEmail1))
            {
                paramArray[0x1f].Value = carrierInfo.BEmail1;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.BEmail2))
            {
                paramArray[0x20].Value = carrierInfo.BEmail2;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf1))
            {
                paramArray[0x21].Value = carrierInfo.Udf1;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf2))
            {
                paramArray[0x22].Value = carrierInfo.Udf2;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf3))
            {
                paramArray[0x23].Value = carrierInfo.Udf3;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf4))
            {
                paramArray[0x24].Value = carrierInfo.Udf4;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf5))
            {
                paramArray[0x25].Value = carrierInfo.Udf5;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf6))
            {
                paramArray[0x26].Value = carrierInfo.Udf6;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf7))
            {
                paramArray[0x27].Value = carrierInfo.Udf7;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf8))
            {
                paramArray[40].Value = carrierInfo.Udf8;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf9))
            {
                paramArray[0x29].Value = carrierInfo.Udf9;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Udf10))
            {
                paramArray[0x2a].Value = carrierInfo.Udf10;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(carrierInfo.Remark))
            {
                paramArray[0x2b].Value = carrierInfo.Remark;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            paramArray[0x2c].Value = carrierInfo.IsActive;
            paramArray[0x2d].Value = carrierInfo.CreatedBy;
            paramArray[0x2e].Value = carrierInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateCarrier(CarrierInfo carrierInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    carrierInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Carrier_Parameters(carrierInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CARRIER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateCarrier(DataBase dataBase, DbTransaction tran, CarrierInfo carrierInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    carrierInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Carrier_Parameters(carrierInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CARRIER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

